package com.njzxw.fs.commonutil.util;

import com.njzxw.fs.commonutil.repository.ResouceReq;
import com.njzxw.fs.commonutil.web.ReturnMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.*;
import java.net.ConnectException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.*;
import java.util.regex.Pattern;

@Service
public class ExcelUtils {

    public org.slf4j.Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private ResouceReq resouceReq;

    @Autowired
    private ReturnMap returnMap;

    /**
     * 要求excel版本在2007以上
     *
     * NotOfficeXmlFileException 不是有效的xlsx文档，可能损坏
     *
     * @param file 文件信息
     * @return
     * @throws Exception
     */
    public List<List<Object>> readExcel(File file) throws Exception {
        if(!file.exists()){
            throw new Exception("找不到文件");
        }
        List<List<Object>> list = new LinkedList<>();
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        // 读取第一张表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = (sheet.getFirstRowNum()); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case STRING:
                        //String类型返回String数据
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        //日期数据返回LONG类型的时间戳
                        if ("yyyy\"年\"m\"月\"d\"日\";@".equals(cell.getCellStyle().getDataFormatString())) {
                            //System.out.println(cell.getNumericCellValue()+":日期格式："+cell.getCellStyle().getDataFormatString());
//                            long longVal = Math.round(cell.getNumericCellValue());
                            value = cell.getCellStyle().getDataFormatString();
//                            value = DateUtils.getFragmentInMinutes(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())) / 1000;
                        } else {
                            //数值类型返回double类型的数字
                            NumberFormat nf = NumberFormat.getInstance();
                            XSSFCell num= row.getCell(0);
                            value = nf.format(num.getNumericCellValue());
                            if (String.valueOf(value).indexOf(",") >= 0) {
                                value = String.valueOf(value).replace(",", "");
                            }
                        }
                        break;
                    case BOOLEAN:
                        //布尔类型
                        value = cell.getBooleanCellValue();
                        break;
                    case BLANK:
                        //空单元格
                        break;
                    default:
                        value = cell.toString();
                }
                if (value != null && !value.equals("")) {
                    //单元格不为空，则加入列表
                    linked.add(value);
                }
            }
            if (linked.size()!= 0) {
                list.add(linked);
            }
        }
        return list;
    }
    /**
     * 要求excel版本在2007以上
     *
     * @param fileInputStream 文件信息
     * @return
     * @throws Exception
     */
    public List<List<Object>> readExcel(InputStream fileInputStream) throws Exception {
        List<List<Object>> list = new LinkedList<List<Object>>();
        XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);
        // 读取第一张表格内容
        XSSFSheet sheet = xwb.getSheetAt(1);
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = (sheet.getFirstRowNum()); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        if ("yyyy\"年\"m\"月\"d\"日\";@".equals(cell.getCellStyle().getDataFormatString())) {
                            //System.out.println(cell.getNumericCellValue()+":日期格式："+cell.getCellStyle().getDataFormatString());
                            value = cell.getCellStyle().getDataFormatString();
//                            value = DateUtils.getMillis(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())) / 1000;
                        } else {
                            NumberFormat nf = NumberFormat.getInstance();
                            XSSFCell num= row.getCell(0);
                            value = nf.format(num.getNumericCellValue());
                            if (String.valueOf(value).indexOf(",") >= 0) {
                                value = String.valueOf(value).replace(",", "");
                            }
                        }
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case BLANK:
                        break;
                    default:
                        value = cell.toString();
                }
                if (value != null && !value.equals("")) {
                    //单元格不为空，则加入列表
                    linked.add(value);
                }
            }
            if (linked.size()!= 0) {
                list.add(linked);
            }
        }
        return list;
    }

    public List<Map<String,Object>> readExcelMap(InputStream fileInputStream) throws Exception {

        return readExcelMap(fileInputStream,null);
    }

    /**
     *
     * @param fileInputStream
     * @param verify 校验规则 [{col:'col1',regex:'\d',msg:'某某错误'}]
     * @return
     * @throws Exception
     */
    public List<Map<String,Object>> readExcelMap(InputStream fileInputStream,List<Map<String,String>> verify) throws Exception {
        List<Map<String,Object>> list = new LinkedList<Map<String,Object>>();
        XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);
        // 读取第一张表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = (sheet.getFirstRowNum()); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Map<String,Object> linked = new HashMap<>();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case STRING:
                            value = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            cell.setCellType(CellType.STRING);
                            if ("yyyy\"年\"m\"月\"d\"日\";@".equals(cell.getCellStyle().getDataFormatString())) {
                                value = cell.getCellStyle().getDataFormatString();
                            } else {
                                value = cell.getStringCellValue();
                            }
                            break;
                        case BOOLEAN:
                            value = cell.getBooleanCellValue();
                            break;
                        case BLANK:
                            break;
                        default:
                            value = cell.toString();
                    }
                }
//               else{
////                    logger.debug("退出：：："+j);
//                    if(i>=row.getLastCellNum()){
//
//                    }
//                    continue;
//                }

                if(StringUtils.isBlank(String.valueOf(value)) || "null".equals(String.valueOf(value))){
                    value = "";
                }
//                logger.debug("=========================col"+j+":=======:"+value);
                linked.put("col"+j,value);

                if(StringUtils.isBlank(StringHelper.get(linked,"errorMsg"))){
//                    logger.debug("========>"+linked);
                    linked.put("errorMsg","");
                }

                if(verify != null){
                    for(int k=0;k<verify.size();k++){
//                        logger.debug("col:"+(("col"+j).equals(StringHelper.get(verify.get(k),"col")))+"==="+(StringHelper.get(verify.get(k),"col"))+"====col"+j);

                        if(("col"+j).equals(StringHelper.get(verify.get(k),"col"))){
                            boolean truth = Pattern.matches(StringHelper.get(verify.get(k),"regex"), String.valueOf(value));
//                            logger.debug("truth:"+truth+"=="+String.valueOf(value)+"=="+StringHelper.get(verify.get(k),"regex"));
                            if(!truth){
//                                logger.debug("加入："+StringHelper.get(verify.get(k),"msg"));
//                                logger.debug("linked:"+linked);
                                linked.put("errorMsg",StringHelper.get(linked,"errorMsg")+","+StringHelper.get(verify.get(k),"msg"));
//                                logger.debug("linked:"+linked);
                            }
                        }
                    }
                }
            }
//            logger.debug("linked====:"+linked);
            list.add(linked);
        }
        return list;
    }

    /**
     * 导出excel
     * @param excel_name 导出的excel路径（需要带.xlsx)
     * @param headList  excel的标题备注名称
     * @param fieldList excel的标题字段（与数据中map中键值对应）
     * @param dataList  excel数据
     * @throws Exception
     */
    public boolean createExcel(String excel_name, String[] headList,
                                   String[] fieldList, List<Map<String, Object>> dataList){
        FileOutputStream fos = null;
        try{
            // 创建新的Excel 工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            // 在Excel工作簿中建一工作表，其名为缺省值
            XSSFSheet sheet = workbook.createSheet();
            // 在索引0的位置创建行（最顶端的行）
            XSSFRow row = sheet.createRow(0);
            // 设置excel头（第一行）的头名称
            for (int i = 0; i < headList.length; i++) {

                // 在索引0的位置创建单元格（左上端）
                XSSFCell cell = row.createCell(i);
                // 定义单元格为字符串类型
                cell.setCellType(CellType.STRING);
                // 在单元格中输入一些内容
                cell.setCellValue(headList[i]);
            }
            // ===============================================================
            //添加数据
            for (int n = 0; n < dataList.size(); n++) {
                // 在索引1的位置创建行（最顶端的行）
                XSSFRow row_value = sheet.createRow(n + 1);
                Map<String, Object> dataMap = dataList.get(n);
                // ===============================================================
                for (int i = 0; i < fieldList.length; i++) {

                    // 在索引0的位置创建单元格（左上端）
                    XSSFCell cell = row_value.createCell(i);
                    // 定义单元格为字符串类型
                    cell.setCellType(CellType.STRING);
                    // 在单元格中输入一些内容
                    cell.setCellValue((dataMap.get(fieldList[i])).toString());
                }
                // ===============================================================
            }
            // 新建一输出文件流
            fos = new FileOutputStream(excel_name);
            // 把相应的Excel 工作簿存盘
            workbook.write(fos);
            fos.flush();
            // 操作结束，关闭文件
//            fos.close();
            return true;
        }catch(Exception e){
            logger.error("导出excel出现错误",e);
            return false;
        }finally {
            if(fos != null){
                try {
                    fos.close();
                } catch (IOException e) {
                }
            }
        }
    }

    @Transactional
    public ReturnMap importTableList(List<Map<String,Object>> list,List<String> verifySql,List<String> insertSql,String tableName){

        return importTableList(list,verifySql,insertSql,tableName,null);
    }

    @Transactional
    public ReturnMap importTableList(List<Map<String,Object>> list,List<String> verifySql,List<String> insertSql,String tableName,ImportTableExecuteInterface importTableExecuteInterface){

        try {
            //进行临时表sql拼接
            if(list != null && !list.isEmpty()){
                int cnt = list.get(0).size();

                //进行数字校验操作
                for(int i=0;i<verifySql.size();i++){
                    String sql = verifySql.get(i).replaceAll("#tmptable",tableName);
                    if(resouceReq.verify(sql) < 0){
                        return returnMap.getErrorReturnMap("数据校验出现错误");
                    }
                }

                if(importTableExecuteInterface != null){
                    importTableExecuteInterface.previous(tableName,list);

                    //执行存在错误
                    if(returnMap.getStates() != null && returnMap.getStates() != ReturnMap.States.SUCCEE){
                        return returnMap;
                    }
                }

                //检查是否存在错误
                int exc = resouceReq.checkError(tableName);
                if(exc > 0 || exc < 0){
                    List<Map<String,Object>> returnList = resouceReq.getErrorMsg(tableName);

                    int sum = resouceReq.getCount(tableName);
                    int errorNum = resouceReq.getErrorCount(tableName);

                    returnMap.setDataMaps(returnList);
                    returnMap.setReturnMap("head",list.get(0));
                    returnMap.setReturnMap("sum",sum);
                    returnMap.setReturnMap("errorNum",errorNum);
                    returnMap.setReturnMap("succeedNum",sum-errorNum);
                    returnMap.setMsgMaps("您导入的数据中存在校验错误，请核对后再进行上传。");
                    return returnMap.getReturnMap(ReturnMap.States.ERROR1);
                }

                int uploadSucceedNum = 0;
                //执行更新语句
                for(int i=0;i<insertSql.size();i++){
                    if(i == 0){
                        uploadSucceedNum = resouceReq.insertInto(insertSql.get(i).replaceAll("#tmptable",tableName));
                        if(uploadSucceedNum < 0){
                            return returnMap.getErrorReturnMap("插入数据失败");
                        }
                    }else {
                        if (resouceReq.insertInto(insertSql.get(i).replaceAll("#tmptable", tableName)) < 0) {
                            return returnMap.getErrorReturnMap("插入数据失败");
                        }
                    }
                }

                if(importTableExecuteInterface != null){
                    importTableExecuteInterface.upper(tableName,list);

                    //执行存在错误
                    if(returnMap.getStates() != null && returnMap.getStates() != ReturnMap.States.SUCCEE){
                        return returnMap;
                    }
                }

                //操作成功，需要展现数据信息
                List<Map<String,Object>> returnList = resouceReq.getErrorMsg(tableName);

                int sum = resouceReq.getCount(tableName);
                int errorNum = resouceReq.getErrorCount(tableName);

                returnMap.setDataMaps(returnList);
                returnMap.setReturnMap("head",list.get(0));
                returnMap.setReturnMap("sum",sum);
                returnMap.setReturnMap("errorNum",errorNum);
                returnMap.setReturnMap("succeedNum",sum-errorNum);
                returnMap.setReturnMap("uploadErrorNum",sum-uploadSucceedNum);
                returnMap.setReturnMap("uploadSucceedNum",uploadSucceedNum);

                return returnMap.getSucceeReturnMap();

            }else{
                return returnMap.getErrorReturnMap("未检测到文件中存在数据");
            }

        }catch (NotOfficeXmlFileException noFileE){
            noFileE.printStackTrace();
            return returnMap.getErrorReturnMap("您上传的文件可能不是标准的office文件");
        }catch (Exception e) {
            e.printStackTrace();
        }

        return returnMap.getErrorReturnMap("未知错误，请稍后再行尝试");
    }

    public interface ImportTableExecuteInterface{

        /**
         * 前期处理校验参数错误信息  直接调用returnMap设置返回值，如果returnMap被设置为错误，后续不会再执行
         * @param tableName 临时表明
         * @param list 原始数据
         */
        void previous(String tableName, List<Map<String, Object>> list);

        /**
         * 执行插入主要sql后的执行路径  直接调用returnMap设置返回值，如果returnMap被设置为错误，后续不会再执行
         * @param tableName 临时表明
         * @param list 原始数据
         */
        void upper(String tableName, List<Map<String, Object>> list);
    }

}
